﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using ProjetoBe.cs;
using System.Data;
using ProjetoDalc.cs;

namespace ProjetoDalc.cs
{
    public class PesquisaDalc
    {
        public void InserirPergunta(PesquisaBe objeto)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                command.Connection = conexao;

                command.CommandText = "INSERT INTO PESQUISA VALUES (@PERGUNTA, @RESPOSTA_UM, @RESPOSTA_DOIS, @RESPOSTA_TRES, @ATIVAR)";
                command.Parameters.AddWithValue("@PERGUNTA", objeto.pergunta);
                command.Parameters.AddWithValue("@RESPOSTA_UM", objeto.respostaUm);
                command.Parameters.AddWithValue("@RESPOSTA_DOIS", objeto.respostaDois);
                if (objeto.respostaTres == "")
                    command.Parameters.AddWithValue("@RESPOSTA_TRES", DBNull.Value);
                else
                    command.Parameters.AddWithValue("@RESPOSTA_TRES", objeto.respostaTres);

                command.Parameters.AddWithValue("@ATIVAR", objeto.ativar);

                adapter.InsertCommand = command;

                conexao.Open();
                command.ExecuteNonQuery();
            }
        }

        public DataTable buscarPesquisa()
        {
            SqlDataReader reader;
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adpter = new SqlDataAdapter();
                command.Connection = conexao;

                command.CommandText = "SELECT * FROM PESQUISA";

                conexao.Open();
                reader = command.ExecuteReader();
                DataTable tabelaPesquisa = new DataTable();
                tabelaPesquisa.Load(reader);
                conexao.Close();                 
                return tabelaPesquisa;

            }
        }

        public string BuscarAtivar(int id)
        {
            PesquisaBe pesquisa = new PesquisaBe();

            using (SqlConnection conexao = Connection.CriarConexao())
            {

                SqlCommand command = new SqlCommand();
                SqlDataAdapter adpter = new SqlDataAdapter();

                command.Connection = conexao;
                command.CommandText = "SELECT ATIVAR FROM PESQUISA WHERE PESQUISA_ID = @ID";
                command.Parameters.AddWithValue("@ID", id);

                conexao.Open();
                string ativo = (string)command.ExecuteScalar();
                conexao.Close();      
   
                return ativo;

            }
        }

        public void AlterarAtivo(PesquisaBe pesquisa)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {

                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                command.Connection = conexao;

                command.CommandText = "UPDATE PESQUISA SET ATIVAR = @ativar WHERE PESQUISA_ID = @id";
                command.Parameters.AddWithValue("@ativar", pesquisa.ativar);
                command.Parameters.AddWithValue("@id", pesquisa.id);

                adapter.InsertCommand = command;
                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();

            }
        }

        public void deletarPesquisa(int id)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                //realiza exclusão da resposta da criança
                command.Connection = conexao;
                command.CommandText = "DELETE FROM RESPOSTA_CRIANCA WHERE PESQUISA_ID = @id";
                command.Parameters.AddWithValue("@id", id);

                adapter.InsertCommand = command;

                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();

                //realiza exclusão da PERGUNTA
                
                command.CommandText = "DELETE FROM PESQUISA WHERE PESQUISA_ID = @id1";
                command.Parameters.AddWithValue("@id1", id);

                adapter.InsertCommand = command;

                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();
                
            }
        }

        public int buscarQuantidadeAtivos()
        {
            PesquisaBe pesquisa = new PesquisaBe();

            using (SqlConnection conexao = Connection.CriarConexao())
            {

                SqlCommand command = new SqlCommand();
                SqlDataAdapter adpter = new SqlDataAdapter();

                command.Connection = conexao;
                command.CommandText = "SELECT COUNT(ATIVAR)FROM PESQUISA WHERE ATIVAR = 'S'";

                conexao.Open();
                int quantidade = (Int32)command.ExecuteScalar();
                conexao.Close();

                return quantidade;

            }
        }

        public List<PesquisaBe> buscarPesquisas()
        {
            List<PesquisaBe> ListaPesquisas = new List<PesquisaBe>();
            try
            {
                using (SqlConnection conexao = Connection.CriarConexao())
                {
                    SqlCommand command = new SqlCommand();
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    SqlDataReader reader;

                    command.Connection = conexao;
                    command.CommandText = "SELECT * FROM PESQUISA WHERE ATIVAR = 'S'";
                    conexao.Open();
                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            PesquisaBe pesquisa = new PesquisaBe();
                            pesquisa.id = Convert.ToInt16(reader["PESQUISA_ID"]);
                            pesquisa.pergunta = reader["PERGUNTA"].ToString();
                            pesquisa.respostaUm = reader["RESPOSTA_UM"].ToString();
                            pesquisa.respostaDois = reader["RESPOSTA_DOIS"].ToString();
                            pesquisa.respostaTres = reader["RESPOSTA_TRES"].ToString();
                            ListaPesquisas.Add(pesquisa);

                        }
                    }
                    conexao.Close();
                }

            }
            catch{}

            return ListaPesquisas;
        }

        public void insereResposta(int radio, int idPesquisa)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                command.Connection = conexao;

                command.CommandText = "INSERT INTO RESPOSTA_CRIANCA VALUES (@PESQUISA_ID, @RESPOSTA_CRIANCA, @RESPOSTA_DATA)";
                command.Parameters.AddWithValue("@pesquisa_id", idPesquisa);
                command.Parameters.AddWithValue("@resposta_crianca", radio);
                command.Parameters.AddWithValue("@resposta_data", DateTime.Now);

                adapter.InsertCommand = command;

                conexao.Open();
                command.ExecuteNonQuery();
            }
        
        }
    }
}
